/*****************************
name:		kayla freeman
date:		8/1/24
purpose:	prep running file of customer loan characteristics from dealscan
******************************/

clear

/*first up - indicator for C has loan, using supplier fiscal year end 
*/

/*prelim step 1: get the s-c pairs gvkeys from pair data; same as in Do2, except keep c_gvkey*/
use 02_supply_chain_links
sort s_gvkey c_gvkey
bysort s_gvkey c_gvkey :keep if _n==1
keep s_gvkey c_gvkey
ren c_gvkey gvkey /*mark this as gvkey for DS merge*/
destring gvkey,replace
destring s_gvkey,replace
sort gvkey s_gvkey
tempfile scg
save `scg'

/*prelim step 2: get fyear end for supps. let's get an indicator for each month-cal-year combo from comp data, and then link to the start and end data for first year and last year. same as in Do2*/

clear
use 01_comp_basic_raw_vars
keep if indfmt=="INDL"
keep gvkey datadate fyear
sort gvkey fyear
drop if missing(fyear)
bysort gvkey fyear: gen dup = cond(_N==1,0,_n)
tab dup /*go ahead and create running months and keep all for each gvkey fyear combo*/
expand 12
sort gvkey fyear dup
bysort gvkey fyear dup: gen month = month(datadate)
bysort gvkey fyear dup: replace month = month[_n-1]-1 if _n>1
gen calyear = year(datadate)
replace calyear = calyear-1 if month<1
replace month = month+12 if month<1
gen date = mdy(month,1,calyear)
format date %td
drop datadate month calyear
drop dup
sort gvkey fyear date
bysort gvkey fyear date: gen dup = cond(_N==1,0,_n)
tab dup /*these are literally dups, so drop extras*/
drop if dup>1
drop dup
sort gvkey date
bysort gvkey date: gen fyearalt = fyear[2]
bysort gvkey date: keep if _n==1
destring gvkey,replace
tempfile months /*file has gvkey fyear date fyearalt*/
save `months' /*will come back to this, use for both facstart and facend*/

/*merge into the DS data. same as in Do2, just for c_gvkeys (tho requires joinby or m:1 merging, since unique s-c pairs), and months merge on s_gvkeys*/
clear
import excel using "DS_Links2018.xlsx", firstrow case(lower) sheet(link_data)

/*drop unnec vars*/
ren discrep_bcoid_flag linkflag
drop company coname_h fic score* smbl* ticker discrep*
sort gvkey
joinby gvkey using `scg'
ren facid facilityid
sort facilityid
tempfile inprog
save `inprog'

clear
use 00_facility 
sort facilityid
joinby facilityid using `inprog'  


/*fix facilitystartdate*/
gen _year = substr(facilitystartdate,1,4)
gen _mo = substr(facilitystartdate,6,2)
gen _day = substr(facilitystartdate,9,2)
destring _year _mo _day,replace
gen altst = mdy(_mo,_day,_year)
format altst %td
drop facilitystartdate _year _mo _day
ren altst facilitystartdate
/*fix facilityenddate*/
gen _year = substr(facilityenddate,1,4)
gen _mo = substr(facilityenddate,6,2)
gen _day = substr(facilityenddate,9,2)
destring _year _mo _day,replace
gen altst = mdy(_mo,_day,_year)
format altst %td
drop facilityenddate _year _mo _day
ren altst facilityenddate

count if facstartdate!=facilitystartdate 
order facstartdate facilitystart /*keep earlier*/
replace facstartdate = facilitystartdate if facilitystartdate<facstartdate

/*drop unnecessary vars*/
drop bcoid   facilitystartdate   loantype    secured     
gen facstart = mdy(month(facstartdate),1,year(facstartdate)) /*first day of mo to match the fyr-end data*/
format facstart %td
drop facstartdate
gen facend = mdy(month(facilityenddate),1,year(facilityenddate)) /*first day of mo to match the fyr-end data*/
format facend %td
drop facilityenddate

ren facstart date
ren gvkey c_gvkey
ren s_gvkey gvkey
merge m:1 gvkey date using `months'
/*for _merge==1, make fyear equal to calyear*/
replace fyear = year(date) if _merge==1
drop if _merge==2
drop _merge
ren date facstart
ren fyear st_fyear
ren fyearalt st_fyearalt

/*before merging in for facend, address issue of missing facend: first, replace with maturity-suggested end, when possible. 
	when maturity is missing too, just let facend = facstart; that way it just gets counted in one year*/
count if missing(facend) & !missing(maturity) 
tab maturity if missing(facend) & !missing(maturity)
gen altend = mofd(facstart)+maturity
format altend %tm
order facstart facend maturity altend
gen altalt =dofm(altend)
format altalt %td
order altalt,after(altend)
replace altend = altalt
drop altalt
replace facend = altend if missing(facend) /*first replace with mat-implied*/
replace facend = facstart if missing(facend) /*next replace with facstart, a.n.*/
drop altend

ren facend date
merge m:1 gvkey date using `months'
replace fyear = year(date) if _merge==1
drop if _merge==2
drop _merge
ren date facend
ren fyear end_fyear
ren fyearalt end_fyearalt
gen yr1 = min(st_fyear ,st_fyearalt)
gen yrN = max(end_fyear,end_fyearalt)
drop st_fyear* end_fyear*
gen numyrs = yrN-yr1+1
expand numyrs
sort facilityid gvkey
gen year = yr1
bysort facilityid gvkey: replace year = year[_n-1]+1 if _n>1
gen hasloan=1
drop currency yr1 yrN facstart facend maturity
capture drop _merge
ren gvkey s_gvkey

sort c_gvkey year
bysort c_gvkey: gen firstfacyr=year[1]
keep c_gvkey s_gvkey hasloan  year firstfacyr
ren hasloan c_hasloan
ren firstfacyr c_firstfacyr
sort c_gvkey s_gvkey year
bysort c_gvkey s_gvkey year: keep if _n==1
save 09_cloan,replace
